﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data.SqlClient;
using System.Data;
namespace DAO
{
    public class NHANVIENDAO
    {
        public bool kiemtradangnhap(string manhanvien)
        {
            SqlConnection conn = DATAPROVIDER.ConnectSQL();
            string sql = "SELECT COUNT(*) FROM NHAN_VIEN WHERE MaNhanVien = '" + manhanvien + "' AND TinhTrang =N'HĐ'";
            SqlCommand sqlcomand = new SqlCommand(sql, conn);
            object temp = sqlcomand.ExecuteScalar();
            int kq = Convert.ToInt32(temp);
            conn.Close();
            conn.Dispose();
            if (kq > 0)
                return true;
            return false;
        }

        public int update(string strquery, DataSet dataset)
        {
            int effrow = 0;
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = strquery;
            SqlConnection conn = DATAPROVIDER.ConnectSQL();
            cmd.Connection = conn;
            SqlDataAdapter sqldataadapter = new SqlDataAdapter(cmd);
            SqlCommandBuilder sqlcomandbuilder = new SqlCommandBuilder(sqldataadapter);
            effrow = sqldataadapter.Update(dataset.Tables["nhanvien"]);
            sqldataadapter.Dispose();
            conn.Close();
            conn.Dispose();
            return effrow;
        }
        public DataTable Bangnhanvien()
        {
            DataTable datatable = new DataTable();
            SqlConnection conn = DATAPROVIDER.ConnectSQL();
            string sql = "SELECT * FROM NHAN_VIEN";
            SqlDataAdapter sqldataadapter = new SqlDataAdapter(sql, conn);
            sqldataadapter.Fill(datatable);
            conn.Close();
            conn.Dispose();
            sqldataadapter.Dispose();
            return datatable;
        }

        public int Themnhanvien(NHANVIENDTO nvDTO)
        {
            //Bước 1: tạo kết nối
            SqlConnection conn = DATAPROVIDER.ConnectSQL();
            //Bước 2: viết câu truy vấn SQL
            string sql = "INSERT INTO NHAN_VIEN(MaNhanVien,HoTen,NgaySinh,DiaChi,DienThoai,MaBoPhan,MaChucVu,MaBangCap,TinhTrang) VALUES(@MNV,@HT,@NS,@DC,@DT,@MBP,@MCV,@MBC,@TT)";
            SqlCommand sqlcommand = new SqlCommand(sql, conn);
            //Bước 3: truyền các giá trị vào các biến trong câu truy vấn sql

            SqlParameter sqlparameter = new SqlParameter("@MNV", System.Data.SqlDbType.NChar, 6);
            sqlparameter.Value = nvDTO.Manhanvien;
            sqlcommand.Parameters.Add(sqlparameter);

            sqlparameter = new SqlParameter("@HT", System.Data.SqlDbType.NVarChar, 50);
            sqlparameter.Value = nvDTO.Hoten.ToUpper();
            sqlcommand.Parameters.Add(sqlparameter);

            sqlparameter = new SqlParameter("@NS", System.Data.SqlDbType.DateTime);
            sqlparameter.Value = nvDTO.Ngaysinh;
            sqlcommand.Parameters.Add(sqlparameter);

            sqlparameter = new SqlParameter("@DC", System.Data.SqlDbType.NVarChar, 100);
            sqlparameter.Value = nvDTO.Diachi.ToUpper();
            sqlcommand.Parameters.Add(sqlparameter);

            sqlparameter = new SqlParameter("@DT", System.Data.SqlDbType.NChar, 11);
            sqlparameter.Value = nvDTO.Dienthoai;
            sqlcommand.Parameters.Add(sqlparameter);

            sqlparameter = new SqlParameter("@MBP", System.Data.SqlDbType.NChar, 3);
            sqlparameter.Value = nvDTO.Mabophan;
            sqlcommand.Parameters.Add(sqlparameter);

            sqlparameter = new SqlParameter("@MCV", System.Data.SqlDbType.NChar, 3);
            sqlparameter.Value = nvDTO.Machucvu;
            sqlcommand.Parameters.Add(sqlparameter);
            sqlparameter = new SqlParameter("@MBC", System.Data.SqlDbType.NChar, 3);
            sqlparameter.Value = nvDTO.Mabangcap;
            sqlcommand.Parameters.Add(sqlparameter);

            sqlparameter = new SqlParameter("@TT", System.Data.SqlDbType.NChar, 3);
            sqlparameter.Value = nvDTO.Tinhtrang;
            sqlcommand.Parameters.Add(sqlparameter);

            int kq = sqlcommand.ExecuteNonQuery();
            conn.Close();
            conn.Dispose();
            if (kq > 0)
            {
                BIENNHANVIENDAO bnvDAO = new BIENNHANVIENDAO();
                bnvDAO.updatebiennhanvien(nvDTO);
            }   
            return kq;

        }
        public string Manhanvien(string Mabophan)
        {
            SqlConnection conn = DATAPROVIDER.ConnectSQL();
            string sql = "SELECT GiaTri FROM BIEN_NHAN_VIEN WHERE BoPhan='" + Mabophan + "'";
            SqlCommand cmd = new SqlCommand(sql, conn);
            object kq2 = cmd.ExecuteScalar();
            int kq = Convert.ToInt32(kq2);
            kq++;
            string kq1 = kq.ToString();
            string temp = "0";
            while(kq1.Length!=3)
                kq1 = temp + kq1;
            Mabophan += kq1;
            conn.Close();
            conn.Dispose();
            return Mabophan;
        }
    }
}
